package cn.iocoder.yudao.framework.excel.core.util;

import cn.idev.excel.write.handler.SheetWriteHandler;
import cn.idev.excel.write.handler.context.SheetWriteHandlerContext;
import cn.iocoder.yudao.framework.excel.core.util.wengfu.FormulaCalculationService;
import cn.iocoder.yudao.framework.excel.core.util.wengfu.ReportDataSaveService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Slf4j
public class PhosphorusOreSheetWriteHandler implements SheetWriteHandler {


    private Map<String, BigDecimal> data;
    private String sheetName;
    private FormulaCalculationService formulaCalculator;
    private ReportDataSaveService dataSaveService;

    // 存储所有单元格的值，用于公式计算
    private Map<String, Object> cellValues = new ConcurrentHashMap<>();
    // 存储所有公式及其位置
    private Map<String, String> formulaMap = new ConcurrentHashMap<>();

    /**
     * 构造器里初始化（只执行一次）
     */
    public PhosphorusOreSheetWriteHandler(Map<String, BigDecimal> data, String sheetName) {
        this.data = data;
        this.sheetName = sheetName;
        this.formulaCalculator = new FormulaCalculationService();
        this.dataSaveService = new ReportDataSaveService();

        // 创建保留两位小数的样式（在 Workbook 创建后再初始化）
        Workbook wb = new XSSFWorkbook();               // 这里仅演示，实际在 afterSheetCreate 中获取真实 workbook
        DataFormat fmt = wb.createDataFormat();
    }



    @Override
    public void afterSheetCreate(SheetWriteHandlerContext context) {
        try {
            Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();
            Sheet sheet = getSheetFromContext(context, workbook);
            if (sheet == null) {
                log.info("无法获取Sheet，跳过表格初始化");
                return;
            }

            System.out.println("开始创建完整表格结构，Sheet: " + sheet.getSheetName());
            setupCompleteTable(workbook, sheet, sheetName);

            // 1. 收集公式
            collectAllFormulas(sheet);

            // 2. 计算并保存（这里已经完成持久化）
            calculateAndSaveFormulas(workbook);

            //
            replaceFormulasWithValues(workbook);

        } catch (Exception e) {
            System.err.println("初始化表格时发生错误: " + e.getMessage());
            e.printStackTrace();
        }
    }



    // 修改收集公式的方法
    private void collectAllFormulas(Sheet sheet) {
        formulaMap.clear();

        for (Row row : sheet) {
            if (row == null) continue;

            for (Cell cell : row) {
                if (cell == null) continue;

                if (cell.getCellType() == CellType.FORMULA) {
                    String cellAddress = getCellAddress(cell);
                    formulaMap.put(cellAddress, cell.getCellFormula());
                }
            }
        }

        log.info("收集到 {} 个公式", formulaMap.size());
    }

    /**
     * 将任意对象（Number、String、BigDecimal 等）保留两位小数并四舍五入。
     * 返回 double，供 POI 的 setCellValue(double) 使用。
     *
     * @param value 待处理的数值对象，null 时返回 0.0
     * @return 四舍五入后保留两位小数的 double
     */
    private double round2(Object value) {
        if (value == null) {
            return 0.0;
        }
        BigDecimal bd;
        if (value instanceof Number) {
            // 直接取 double 再转 BigDecimal，避免精度误差
            bd = new BigDecimal(((Number) value).doubleValue());
        } else {
            try {
                bd = new BigDecimal(value.toString());
            } catch (Exception e) {
                // 解析失败统一视为 0
                return 0.0;
            }
        }
        // HALF_UP = 四舍五入
        return bd.setScale(2, RoundingMode.HALF_UP).doubleValue();
    }


    /**
     * 计算工作簿中所有已收集的公式，并把结果（四舍五入后）保存到 cellValues 中。
     * 这里把 null / 错误结果统一兜底为 0.0，方便后续 replaceFormulasWithValues 使用。
     */
    private void calculateAndSaveFormulas(Workbook workbook) {
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

        // 这里直接把四舍五入后的数值放进 cellValues，后面写回时不需要再次处理
        for (Map.Entry<String, String> entry : formulaMap.entrySet()) {
            String address = entry.getKey();                     // 例如 "D8"
            CellReference ref = new CellReference(address);
            Sheet sheet = workbook.getSheet(sheetName);
            if (sheet == null) {
                log.warn("未找到 sheet {}，公式 {} 跳过", sheetName, address);
                continue;
            }
            Row row = sheet.getRow(ref.getRow());
            if (row == null) {
                log.warn("未找到行 {}，公式 {} 跳过", ref.getRow(), address);
                continue;
            }
            Cell cell = row.getCell(ref.getCol());
            if (cell == null) {
                log.warn("未找到单元格 {}，公式 {} 跳过", address, entry.getValue());
                continue;
            }

            CellValue cv = evaluator.evaluate(cell);
            Object rawValue = null;
            if (cv != null) {
                switch (cv.getCellType()) {
                    case NUMERIC:
                        rawValue = cv.getNumberValue();
                        break;
                    case STRING:
                        rawValue = cv.getStringValue();
                        break;
                    case BOOLEAN:
                        rawValue = cv.getBooleanValue();
                        break;
                    case ERROR:
                        rawValue = null;
                        break;
                    default:
                        rawValue = null;
                }
            }

            // ---- 关键：四舍五入处理 ----
            if (rawValue instanceof Number) {
                double rounded = round2(rawValue);
                cellValues.put(address, rounded);
            } else if (rawValue != null) {
                // 非数值（如字符串）直接保存
                cellValues.put(address, rawValue);
            } else {
                // 计算结果为 null / 错误，使用 0.0 兜底
                cellValues.put(address, 0.0);
                log.warn("公式 {} 计算结果为 null，已使用 0.0 兜底", address);
            }
        }

        log.info("成功计算并保存 {} 条公式（已四舍五入）", cellValues.size());
    }

    /**
     * 把工作簿中所有公式单元格彻底替换为已经四舍五入的数值。
     * 1. 读取 cellValues 中缓存的结果（已经四舍五入）；
     * 2. 用普通数值覆盖公式单元格，并清除公式；
     * 3. 保持原有样式不变；
     * 4. 关闭强制重新计算，防止 Excel 再次计算公式。
     */
    private void replaceFormulasWithValues(Workbook workbook) {
        // 关闭工作簿的强制公式重新计算（如果之前被打开，这里统一关闭）
        workbook.setForceFormulaRecalculation(false);

        for (Sheet sheet : workbook) {
            for (Row row : sheet) {
                if (row == null) continue;

                for (Cell cell : row) {
                    if (cell == null) continue;

                    if (cell.getCellType() == CellType.FORMULA) {
                        String cellAddress = getCellAddress(cell);
                        Object calculatedValue = cellValues.get(cellAddress);

                        if (calculatedValue != null) {
                            // ① 先保存原始样式
                            CellStyle originalStyle = cell.getCellStyle();

                            // ② 清除公式，强制把单元格类型改为 NUMERIC / STRING / BOOLEAN
                            // POI 没有直接的 “clearFormula” 方法，最简办法是先创建一个新单元格
                            // 然后把值写进去，再把样式复制回去
                            Cell newCell = row.createCell(cell.getColumnIndex(), CellType.BLANK);
                            // 写入四舍五入后的数值
                            if (calculatedValue instanceof Number) {
                                double rounded = round2(calculatedValue);   // 再次确保四舍五入
                                newCell.setCellValue(rounded);
                            } else if (calculatedValue instanceof String) {
                                newCell.setCellValue((String) calculatedValue);
                            } else if (calculatedValue instanceof Boolean) {
                                newCell.setCellValue((Boolean) calculatedValue);
                            } else {
                                newCell.setCellValue(String.valueOf(calculatedValue));
                            }

                            // ③ 恢复样式
                            newCell.setCellStyle(originalStyle);
                        }
                    }
                }
            }
        }
        log.info("已完成公式替换为四舍五入后的数值（公式已清除）");
    }


    /**
     * 获取单元格地址
     */
    private String getCellAddress(Cell cell) {
        return CellReference.convertNumToColString(cell.getColumnIndex()) + (cell.getRowIndex() + 1);
    }

    /**
     * 获取单元格值
     */
    private Object getCellValue(Cell cell) {
        switch (cell.getCellType()) {
            case NUMERIC:
                return cell.getNumericCellValue();
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                return cell.getCellFormula();
            default:
                return 0.0;
        }
    }



    private Sheet getSheetFromContext(SheetWriteHandlerContext context, Workbook workbook) {
        try {
            try {
                return (Sheet) context.getClass().getMethod("getSheet").invoke(context);
            } catch (Exception e) {
                try {
                    return context.getWriteSheetHolder().getSheet();
                } catch (Exception ex) {
                    String sheetName = context.getWriteSheetHolder().getSheetName();
                    Sheet sheet = workbook.getSheet(sheetName);
                    return sheet != null ? sheet : workbook.createSheet(sheetName);
                }
            }
        } catch (Exception e) {
            return workbook.createSheet("矿石质量数据");
        }
    }

    private void setupCompleteTable(Workbook workbook, Sheet sheet,String sheetName) throws InvocationTargetException, NoSuchMethodException, IllegalAccessException {
        // 创建所有需要的行（根据实际表格行数调整）
        createAllRows(sheet, 60);

        // 设置表头
        setupHeaderRows(workbook, sheet);

        // 设置所有数据行
        setupAllDataRows(sheet);

        // 应用所有合并单元格
        applyAllMergeRegions(sheet);
    }

    private void createAllRows(Sheet sheet, int maxRows) {
        for (int i = 0; i <= maxRows; i++) {
            getOrCreateRow(sheet, i);
        }
    }

    private void setupHeaderRows(Workbook workbook, Sheet sheet) {
        System.out.println("设置表头行...");
        // 设置列宽（单位：1/256字符宽度）
        sheet.setColumnWidth(0, 8 * 256);   // 序号列
        sheet.setColumnWidth(1, 15 * 256);  // 产品名称列
        sheet.setColumnWidth(2, 18 * 256);  // 品级列
        sheet.setColumnWidth(3, 18 * 256);  // 本月-平均
        sheet.setColumnWidth(4, 18 * 256);  // 本月-数量(t)
        sheet.setColumnWidth(5, 18 * 256);  // 本年累计-平均
        sheet.setColumnWidth(6, 18 * 256);  // 本年累计-数量(t)
        sheet.setColumnWidth(7, 18 * 256);  // 去年同期累计-平均
        sheet.setColumnWidth(8, 18 * 256);  // 去年同期累计-数量(t)
//        sheet.setColumnWidth(9, 3 * 256);   // 空列（间隔列）
//        sheet.setColumnWidth(10, 10 * 256); // 年累计-平均
//        sheet.setColumnWidth(11, 12 * 256); // 年累计-数量(t)

        // 创建黑色边框样式
        CellStyle blackBorderStyle = createBlackBorderStyle(workbook);
        CellStyle titleStyle = createTitleStyle(workbook);

        // 第1行（索引0）：标题行
        Row titleRow = getOrCreateRow(sheet, 0);
        titleRow.setHeightInPoints(25);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("委托加工及购进矿石质量报表");
        titleCell.setCellStyle(titleStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));

        // 为标题行合并区域的所有单元格设置边框
        setMergedRegionBorder(sheet, 0, 0, 0, 8, blackBorderStyle);

        // 第2行（索引1）：第二行表头
        Row secondHeaderRow = getOrCreateRow(sheet, 1);
        secondHeaderRow.setHeightInPoints(20);
        String[] headers2 = {"序号", "产品名称", "品级", "本月", "", "本年累计", "", "去年同期累计", "", };
        for (int i = 0; i < headers2.length; i++) {
            Cell cell = secondHeaderRow.createCell(i);
            cell.setCellValue(headers2[i]);
            cell.setCellStyle(blackBorderStyle);
        }

        // 第3行（索引2）：第三行表头
        Row thirdHeaderRow = getOrCreateRow(sheet, 2);
        thirdHeaderRow.setHeightInPoints(20);
        String[] headers3 = {"", "", "", "平均", "数量（t)", "平均", "数量（t)", "平均", "数量（t)"};
        for (int i = 0; i < headers3.length; i++) {
            Cell cell = thirdHeaderRow.createCell(i);
            cell.setCellValue(headers3[i]);
            cell.setCellStyle(blackBorderStyle);
        }

        // 设置合并区域
        setupMergedRegions(sheet, blackBorderStyle);
    }
    /**
     * 创建黑色边框样式
     */
    private CellStyle createBlackBorderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 设置黑色边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());

        style.setWrapText(true);
        return style;
    }
    /**
     * 创建标题样式
     */
    private CellStyle createTitleStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 14);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 设置黑色边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());

        return style;
    }

    /**
     * 设置合并区域并确保边框完整
     */
    private void setupMergedRegions(Sheet sheet, CellStyle borderStyle) {
        // 垂直合并：序号、产品名称、品级（合并第1-2行）
        sheet.addMergedRegion(new CellRangeAddress(1, 3, 0, 0)); // 序号（A列）
        sheet.addMergedRegion(new CellRangeAddress(1, 3, 1, 1)); // 产品名称（B列）
        sheet.addMergedRegion(new CellRangeAddress(1, 3, 2, 2)); // 品级（C列）


        // 垂直合并：平均和数量（t）列（第3行和第4行）
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 3, 3));  // 第三行"平均"和"数量（t)"的垂直合并（D列）
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 4, 4));  // 第三行"数量（t)"的垂直合并（E列）
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 5, 5));  // 第三行"平均"和"数量（t)"的垂直合并（F列）
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 6, 6));  // 第三行"数量（t)"的垂直合并（G列）
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 7, 7));  // 第三行"平均"和"数量（t)"的垂直合并（H列）
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 8, 8));  // 第三行"数量（t)"的垂直合并（I列）
//        sheet.addMergedRegion(new CellRangeAddress(2, 3, 10, 10));  // 第三行"平均"和"数量（t)"的垂直合并（K列）
//        sheet.addMergedRegion(new CellRangeAddress(2, 3, 11, 11));  // 第三行"数量（t)"的垂直合并（L列）

        // 水平合并：本月、本年累计、去年同期累计、年累计（合并第1行的多列）
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 3, 4));  // 本月（D-E列）
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 5, 6));  // 本年累计（F-G列）
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 7, 8));  // 去年同期累计（H-I列）
//        sheet.addMergedRegion(new CellRangeAddress(1, 1, 10, 11)); // 年累计（K-L列）

        // 为所有合并区域设置完整边框
        setMergedRegionBorder(sheet, 1, 3, 0, 0, borderStyle); // 序号列
        setMergedRegionBorder(sheet, 1, 3, 1, 1, borderStyle); // 产品名称列
        setMergedRegionBorder(sheet, 1, 3, 2, 2, borderStyle); // 品级列

        setMergedRegionBorder(sheet, 1, 1, 3, 4, borderStyle); // 本月
        setMergedRegionBorder(sheet, 1, 1, 5, 6, borderStyle); // 本年累计
        setMergedRegionBorder(sheet, 1, 1, 7, 8, borderStyle); // 去年同期累计
//        setMergedRegionBorder(sheet, 1, 1, 10, 11, borderStyle); // 年累计


        setMergedRegionBorder(sheet,2, 3, 3, 3, borderStyle);  // 第三行"平均"和"数量（t)"的垂直合并（D列）
        setMergedRegionBorder(sheet,2, 3, 4, 4, borderStyle);  // 第三行"数量（t)"的垂直合并（E列）
        setMergedRegionBorder(sheet,2, 3, 5, 5, borderStyle);  // 第三行"平均"和"数量（t)"的垂直合并（F列）
        setMergedRegionBorder(sheet,2, 3, 6, 6, borderStyle);  // 第三行"数量（t)"的垂直合并（G列）
        setMergedRegionBorder(sheet,2, 3, 7, 7, borderStyle);  // 第三行"平均"和"数量（t)"的垂直合并（H列）
        setMergedRegionBorder(sheet,2, 3, 8, 8, borderStyle);  // 第三行"数量（t)"的垂直合并（I列）
//        setMergedRegionBorder(sheet,2, 3, 10, 10, borderStyle);  // 第三行"平均"和"数量（t)"的垂直合并（K列）
//        setMergedRegionBorder(sheet,2, 3, 11, 11, borderStyle);  // 第三行"数量（t)"的垂直合并（L列）


    }

    /**
     * 为合并区域的所有单元格设置边框（解决POI合并单元格边框不完整的问题）
     */
    private void setMergedRegionBorder(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, CellStyle borderStyle) {
        for (int rowNum = firstRow; rowNum <= lastRow; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) {
                row = sheet.createRow(rowNum);
            }
            for (int colNum = firstCol; colNum <= lastCol; colNum++) {
                Cell cell = row.getCell(colNum);
                if (cell == null) {
                    cell = row.createCell(colNum);
                }
                // 创建新的样式实例，避免样式共享问题
                CellStyle newStyle = sheet.getWorkbook().createCellStyle();
                newStyle.cloneStyleFrom(borderStyle);
                cell.setCellStyle(newStyle);
            }
        }
    }


    private void setupAllDataRows(Sheet sheet) throws InvocationTargetException, NoSuchMethodException, IllegalAccessException {
        System.out.println("设置所有数据行结构...");

        // 一、受托加工磷矿石 (行索引4-6) - 原来3-5，现在+1
        setupFirstCategoryRow(getOrCreateRow(sheet, 4), "一", "受托加工磷矿石", "P2O5(%)");
        setupFirstCategorySubRow(getOrCreateRow(sheet, 5), "R2O3(%)");
        setupFirstCategorySubRow(getOrCreateRow(sheet, 6), "SiO2(%)");

        // 1 黄磷公司 (行索引7-9) - 原来6-8，现在+1
        setupHuanglinCompanyRow(getOrCreateRow(sheet, 7), "1", "黄磷公司", "P2O5(%)");
        setupHuanglinCompanySubRow(getOrCreateRow(sheet, 8), "R2O3(%)");
        setupHuanglinCompanySubRow(getOrCreateRow(sheet, 9), "SiO2(%)");

        // 1.1 a层矿 (行索引10-12) - 原来9-11，现在+1
        setupALayerRow(getOrCreateRow(sheet, 10), "1.1", "a层矿", "P2O5(%)");
        setupALayerSubRow(getOrCreateRow(sheet, 11), "R2O3(%)");
        setupALayerSubRow(getOrCreateRow(sheet, 12), "SiO2(%)");

        // 其中a层矿返粉 (行索引13-15) - 原来12-14，现在+1
        setupALayerReturnPowderRow(getOrCreateRow(sheet, 13), "其中a层矿返粉", "P2O5(%)");
        setupALayerReturnPowderSubRow(getOrCreateRow(sheet, 14), "R2O3(%)");
        setupALayerReturnPowderSubRow(getOrCreateRow(sheet, 15), "SiO2(%)");

        // 1.2 b层矿 (行索引16-18) - 原来15-17，现在+1
        setupBLayerRow(getOrCreateRow(sheet, 16), "1.2", "b层矿", "P2O5(%)");
        setupBLayerSubRow(getOrCreateRow(sheet, 17), "R2O3(%)");
        setupBLayerSubRow(getOrCreateRow(sheet, 18), "SiO2(%)");

        // 其中划拨b层矿 (行索引19-21) - 原来18-20，现在+1
        setupAssignedBLayerRow(getOrCreateRow(sheet, 19), "其中划拨b层矿", "P2O5(%)");
        setupAssignedBLayerSubRow(getOrCreateRow(sheet, 20), "R2O3(%)");
        setupAssignedBLayerSubRow(getOrCreateRow(sheet, 21), "SiO2(%)");

        // 二、购进磷矿石 (行索引22-25) - 原来21-24，现在+1
        setupPurchaseOreRow(getOrCreateRow(sheet, 22), "二", "购进磷矿石", "P2O5(%)");
        setupPurchaseOreSubRow(getOrCreateRow(sheet, 23), "MgO(%)");
        setupPurchaseOreSubRow(getOrCreateRow(sheet, 24), "R2O3(%)");
        setupPurchaseOreSubRow(getOrCreateRow(sheet, 25), "SiO2(%)");

        // 1 自产矿石 (行索引26-29) - 原来25-28，现在+1
        setupSelfProducedOreRow(getOrCreateRow(sheet, 26), "1", "自产矿石", "P2O5(%)");
        setupSelfProducedOreSubRow(getOrCreateRow(sheet, 27), "MgO(%)");
        setupSelfProducedOreSubRow(getOrCreateRow(sheet, 28), "R2O3(%)");
        setupSelfProducedOreSubRow(getOrCreateRow(sheet, 29), "SiO2(%)");

        // 2 外部矿石利用 (行索引30-33) - 原来29-32，现在+1
        setupExternalOreRow(getOrCreateRow(sheet, 30), "2", "外部矿石利用", "P2O5(%)");
        setupExternalOreSubRow(getOrCreateRow(sheet, 31), "MgO(%)");
        setupExternalOreSubRow(getOrCreateRow(sheet, 32), "R2O3(%)");
        setupExternalOreSubRow(getOrCreateRow(sheet, 33), "SiO2(%)");

        // 1 双山坪公司 (行索引34-37) - 原来33-36，现在+1
        setupShuangshanpingRow(getOrCreateRow(sheet, 34), "1", "双山坪公司", "P2O5(%)");
        setupShuangshanpingSubRow(getOrCreateRow(sheet, 35), "MgO(%)");
        setupShuangshanpingSubRow(getOrCreateRow(sheet, 36), "R2O3(%)");
        setupShuangshanpingSubRow(getOrCreateRow(sheet, 37), "SiO2(%)");

        // 1.1 自产矿石 (行索引38-41) - 原来37-40，现在+1
        setupShuangshanpingSelfProducedRow(getOrCreateRow(sheet, 38), "1.1", "自产矿石", "P2O5(%)");
        setupShuangshanpingSelfProducedSubRow(getOrCreateRow(sheet, 39), "MgO(%)");
        setupShuangshanpingSelfProducedSubRow(getOrCreateRow(sheet, 40), "R2O3(%)");
        setupShuangshanpingSelfProducedSubRow(getOrCreateRow(sheet, 41), "SiO2(%)");

        // 1.2 外部矿石利用 (行索引42-45) - 原来41-44，现在+1
        setupShuangshanpingExternalOreRow(getOrCreateRow(sheet, 42), "1.2", "外部矿石利用", "P2O5(%)");
        setupShuangshanpingExternalOreSubRow(getOrCreateRow(sheet, 43), "MgO(%)");
        setupShuangshanpingExternalOreSubRow(getOrCreateRow(sheet, 44), "R2O3(%)");
        setupShuangshanpingExternalOreSubRow(getOrCreateRow(sheet, 45), "SiO2(%)");

        // 2 有福公司 (行索引46-49) - 原来45-48，现在+1
        setupYoufuCompanyRow(getOrCreateRow(sheet, 46), "2", "有福公司", "P2O5(%)");
        setupYoufuCompanySubRow(getOrCreateRow(sheet, 47), "MgO(%)");
        setupYoufuCompanySubRow(getOrCreateRow(sheet, 48), "R2O3(%)");
        setupYoufuCompanySubRow(getOrCreateRow(sheet, 49), "SiO2(%)");

        // 2.1 自产矿石 (行索引50-53) - 原来49-52，现在+1
        setupYoufuSelfProducedRow(getOrCreateRow(sheet, 50), "2.1", "自产矿石", "P2O5(%)");
        setupYoufuSelfProducedSubRow(getOrCreateRow(sheet, 51), "MgO(%)");
        setupYoufuSelfProducedSubRow(getOrCreateRow(sheet, 52), "R2O3(%)");
        setupYoufuSelfProducedSubRow(getOrCreateRow(sheet, 53), "SiO2(%)");

        // 2.2 外部矿石利用 (行索引54-57) - 原来53-56，现在+1
        setupYoufuExternalOreRow(getOrCreateRow(sheet, 54), "2.2", "外部矿石利用", "P2O5(%)");
        setupYoufuExternalOreSubRow(getOrCreateRow(sheet, 55), "MgO(%)");
        setupYoufuExternalOreSubRow(getOrCreateRow(sheet, 56), "R2O3(%)");
        setupYoufuExternalOreSubRow(getOrCreateRow(sheet, 57), "SiO2(%)");
    }

    private Row getOrCreateRow(Sheet sheet, int rowIndex) {
        Row row = sheet.getRow(rowIndex);
        if (row == null) {
            row = sheet.createRow(rowIndex);
        }
        return row;
    }

    // === 设置单元格公式的辅助方法 ===
    // 修改你的 setCellFormula 方法，记录公式
    private void setCellFormula(Row row, int columnIndex, String formula) {
        if (formula == null || formula.isEmpty()) return;

        // ① 先把 ISERROR 替换为 IFERROR
        String fixed = rewriteIsError(formula);

        // ② 处理外部工作簿引用（直接置 0，或改为本工作簿占位单元格）
        fixed = fixed.replaceAll("\\[\\d+\\][^!]+!", "0");   // 把 "[1]受托原矿数据源!AA7" → "0"

        Cell cell = row.createCell(columnIndex);
        cell.setCellFormula(fixed);

        // 记录公式（后面求值会用到）
        String cellAddress = getCellAddress(cell);
        formulaMap.put(cellAddress, fixed);
    }

    /**
     * 把公式中的 ISERROR 包装改写为 IFERROR。
     * 例：IF(ISERROR(A1/B1),0,(A1/B1))  →  IFERROR(A1/B1,0)
     */
    private String rewriteIsError(String formula) {
        // 只处理最常见的 “IF(ISERROR(...),0,…)” 形式
        Pattern p = Pattern.compile(
                "IF\\s*\\(\\s*ISERROR\\s*\\(([^\\)]+)\\)\\s*,\\s*0\\s*,\\s*\\(([^\\)]+)\\)\\s*\\)",
                Pattern.CASE_INSENSITIVE);
        Matcher m = p.matcher(formula);
        StringBuffer sb = new StringBuffer();
        while (m.find()) {
            String inner = m.group(1).trim();          // 被检测的表达式
            // 直接使用 IFERROR(inner,0)
            String replacement = "IFERROR(" + inner + ",0)";
            m.appendReplacement(sb, replacement);
        }
        m.appendTail(sb);
        return sb.toString();
    }


    /**
     * 写入 double 类型数值，自动四舍五入保留两位小数。
     */
    private void setCellValue(Row row, int columnIndex, double value) {
        double rounded = round2(value);               // 四舍五入
        Cell cell = row.createCell(columnIndex);
        cell.setCellValue(rounded);
        // 同步到缓存，后续 replaceFormulasWithValues 直接使用
        cellValues.put(getCellAddress(cell), rounded);
    }

    /**
     * 写入 BigDecimal 类型数值，先转 double 再四舍五入。
     */
    private void setCellValue(Row row, int columnIndex, BigDecimal value) {
        if (value != null) {
            setCellValue(row, columnIndex, value.doubleValue());
        } else {
            setCellValue(row, columnIndex, 0.0);
        }
    }

    /**
     * 写入 String 类型数值（不做四舍五入）。
     */
    private void setCellValue(Row row, int columnIndex, String value) {
        Cell cell = row.createCell(columnIndex);
        cell.setCellValue(value);
        cellValues.put(getCellAddress(cell), value);
    }


    // === 一、受托加工磷矿石 ===
    private void setupFirstCategoryRow(Row row, String sequence, String productName, String grade) {
        setCellValue(row, 0, sequence);
        setCellValue(row, 1, productName);
        setCellValue(row, 2, grade);

        setCellFormula(row, 3, "D8");
        setCellFormula(row, 4, "E8");
        setCellFormula(row, 5, "(E5*D5+K5*L5)/G5");
        setCellFormula(row, 6, "E5+L5");

        setCellValue(row, 7, 24.994045906623);
        setCellValue(row, 8, 2203269.48);
//        setCellValue(row, 10, 24.9130977475979);
//        setCellValue(row, 11, 2392738.29);
    }

    private void setupFirstCategorySubRow(Row row, String grade) {
        setCellValue(row, 2, grade);

        if ("R2O3(%)".equals(grade)) {
            setCellFormula(row, 3, "D9");
            setCellFormula(row, 5, "(E5*D6+K6*L5)/G5");
            setCellValue(row, 7, 2.73879442358919);
//            setCellValue(row, 10, 2.74709090103332);
        } else if ("SiO2(%)".equals(grade)) {
            setCellFormula(row, 3, "D10");
            setCellFormula(row, 5, "(E5*D7+K7*L5)/G5");
            setCellValue(row, 7, 10.3608083679202);
//            setCellValue(row, 10, 10.4085350116002);
        }
    }

    // === 1 黄磷公司 ===
    private void setupHuanglinCompanyRow(Row row, String sequence, String productName, String grade) {
        setCellValue(row, 0, sequence);
        setCellValue(row, 1, productName);
        setCellValue(row, 2, grade);

        setCellValue(row, 3, "委托加工及购进磷矿石明细F7");
        setCellValue(row, 4, "委托加工及购进磷矿石明细C7");
        setCellFormula(row, 5, "(E8*D8+K8*L8)/G8");
        setCellFormula(row, 6, "E8+L8");

        setCellValue(row, 7, 24.994045906623);
        setCellValue(row, 8, 2203269.48);
//        setCellValue(row, 10, 24.9130977475979);
//        setCellValue(row, 11, 2392738.29);
    }

    private void setupHuanglinCompanySubRow(Row row, String grade) {
        setCellValue(row, 2, grade);

        if ("R2O3(%)".equals(grade)) {
            setCellValue(row, 3, "委托加工及购进磷矿石明细H7");
            setCellFormula(row, 5, "(E8*D9+K9*L8)/G8");
            setCellValue(row, 7, 2.73879442358919);
//            setCellValue(row, 10, 2.74709090103332);
        } else if ("SiO2(%)".equals(grade)) {
            setCellValue(row, 3, "委托加工及购进磷矿石明细I7");
            setCellFormula(row, 5, "(E8*D10+L8*K10)/G8");
            setCellValue(row, 7, 10.3608083679202);
//            setCellValue(row, 10, 10.4085350116002);
        }
    }

    // === 1.1 a层矿 ===
    private void setupALayerRow(Row row, String sequence, String productName, String grade) {
        setCellValue(row, 0, sequence);
        setCellValue(row, 1, productName);
        setCellValue(row, 2, grade);

        setCellValue(row, 3, "委托加工及购进磷矿石明细F8");
        setCellValue(row, 4, "委托加工及购进磷矿石明细C8");
        setCellFormula(row, 5, "(E11*D11+K11*L11)/G11");
        setCellFormula(row, 6, "E11+L11");

        setCellValue(row, 7, 25.595434289195);
        setCellValue(row, 8, 1219506.49);
//        setCellValue(row, 10, 25.3142242196762);
//        setCellValue(row, 11, 1175006.01);
    }

    private void setupALayerSubRow(Row row, String grade) {
        setCellValue(row, 2, grade);

        if ("R2O3(%)".equals(grade)) {
            setCellValue(row, 3, "委托加工及购进磷矿石明细H8");
            setCellFormula(row, 5, "(E11*D12+K12*L11)/G11");
            setCellValue(row, 7, 3.93460641284686);
//            setCellValue(row, 10, 4.23563473463541);
        } else if ("SiO2(%)".equals(grade)) {
            setCellValue(row, 3, "委托加工及购进磷矿石明细I8");
            setCellFormula(row, 5, "(E11*D13+L11*K13)/G11");
            setCellValue(row, 7, 13.9536124007034);
//            setCellValue(row, 10, 14.984569104306);
        }
    }

    // === 其中a层矿返粉 ===
    private void setupALayerReturnPowderRow(Row row, String productName, String grade) {
        setCellValue(row, 1, productName);
        setCellValue(row, 2, grade);

        setCellValue(row, 3, "委托加工及购进磷矿石明细F19");
        setCellValue(row, 4, "委托加工及购进磷矿石明细C19");
        setCellFormula(row, 5, "IF(ISERROR((E14*D14+K14*L14)/G14),\"0.00\",((E14*D14+K14*L14)/G14))");
        setCellFormula(row, 6, "E14+L14");

        setCellValue(row, 7, 25.0);
        setCellValue(row, 8, 9426.47);
//        setCellValue(row, 10, 25.0);
//        setCellValue(row, 11, 3166.51);
    }

    private void setupALayerReturnPowderSubRow(Row row, String grade) {
        setCellValue(row, 2, grade);

        if ("R2O3(%)".equals(grade)) {
            setCellValue(row, 3, "委托加工及购进磷矿石明细H19");
            setCellFormula(row, 5, "IF(ISERROR((E14*D15+K15*L14)/G14),\"0.00\",((E14*D15+K15*L14)/G14))");
            setCellValue(row, 7, 6.35195616174453);
//            setCellValue(row, 10, 3.91447130255073);
        } else if ("SiO2(%)".equals(grade)) {
            setCellValue(row, 3, "委托加工及购进磷矿石明细I19");
            setCellFormula(row, 5, "IF(ISERROR((E14*D16+K16*L14)/G14),\"0.00\",((E14*D16+K16*L14)/G14))");
            setCellValue(row, 7, 24.2531608014453);
//            setCellValue(row, 10, 14.5156842287913);
        }
    }

    // === 1.2 b层矿 ===
    private void setupBLayerRow(Row row, String sequence, String productName, String grade) {
        setCellValue(row, 0, sequence);
        setCellValue(row, 1, productName);
        setCellValue(row, 2, grade);

        setCellValue(row, 3, "委托加工及购进磷矿石明细F9");
        setCellValue(row, 4, "委托加工及购进磷矿石明细C9");
        setCellFormula(row, 5, "(E17*D17+K17*L17)/G17");
        setCellFormula(row, 6, "E17+L17");

        setCellValue(row, 7, 24.2485441516148);
        setCellValue(row, 8, 983762.99);
//        setCellValue(row, 10, 24.5260455003978);
//        setCellValue(row, 11, 1217732.28);
    }

    private void setupBLayerSubRow(Row row, String grade) {
        setCellValue(row, 2, grade);

        if ("R2O3(%)".equals(grade)) {
            setCellValue(row, 3, "委托加工及购进磷矿石明细H9");
            setCellFormula(row, 5, "(E17*D18+K18*L17)/G17");
            setCellValue(row, 7, 1.25642468967641);
//            setCellValue(row, 10, 1.31077523513761);
        } else if ("SiO2(%)".equals(grade)) {
            setCellValue(row, 3, "委托加工及购进磷矿石明细I9");
            setCellFormula(row, 5, "(E17*D19+L17*K19)/G17");
            setCellValue(row, 7, 5.90704472788199);
//            setCellValue(row, 10, 5.9930590903293);
        }
    }

    // === 其中划拨b层矿 ===
    private void setupAssignedBLayerRow(Row row, String productName, String grade) {
        setCellValue(row, 1, productName);
        setCellValue(row, 2, grade);

        setCellValue(row, 3, "受托原矿数据源!AA7");
        setCellValue(row, 4, "受托原矿数据源!Z7");
        setCellFormula(row, 5, "IF(ISERROR((E20*D20+K20*L20)/G20),0,((E20*D20+K20*L20)/G20))");
        setCellFormula(row, 6, "E20+L20");

        setCellValue(row, 7, 23.5645761086306);
        setCellValue(row, 8, 35000.0);
//        setCellValue(row, 10, 25.0);
//        setCellValue(row, 11, 80256.5);
    }

    private void setupAssignedBLayerSubRow(Row row, String grade) {
        setCellValue(row, 2, grade);

        if ("R2O3(%)".equals(grade)) {
            setCellValue(row, 3, "受托原矿数据源!AC7");
            setCellFormula(row, 5, "IF(ISERROR((E20*D21+K21*L20)/G20),0,((E20*D21+K21*L20)/G20))");
            setCellValue(row, 7, 1.28987829988296);
//            setCellValue(row, 10, 0.761817053945024);
        } else if ("SiO2(%)".equals(grade)) {
            setCellValue(row, 3, "受托原矿数据源!AD7");
            setCellFormula(row, 5, "IF(ISERROR((E20*D22+K22*L20)/G20),0,((E20*D22+K22*L20)/G20))");
            setCellValue(row, 7, 6.89860044865404);
//            setCellValue(row, 10, 3.23049458495166);
        }
    }

    // === 二、购进磷矿石 ===
    private void setupPurchaseOreRow(Row row, String sequence, String productName, String grade) {
        setCellValue(row, 0, sequence);
        setCellValue(row, 1, productName);
        setCellValue(row, 2, grade);

        setCellFormula(row, 3, "IF(ISERROR((D27*E27+D31*E31)/E23),0,((D27*E27+D31*E31)/E23))");
        setCellFormula(row, 4, "E27+E31");
        setCellFormula(row, 5, "(E23*D23+K23*L23)/G23");
        setCellFormula(row, 6, "E23+L23");

        setCellValue(row, 7, 23.1714509243731);
        setCellValue(row, 8, 369937.2);
    }

    private void setupPurchaseOreSubRow(Row row, String grade) {
        setCellValue(row, 2, grade);

        if ("MgO(%)".equals(grade)) {
            setCellFormula(row, 3, "IF(ISERROR((D28*E27+D32*E31)/E23),0,((D28*E27+D32*E31)/E23))");
            setCellFormula(row, 5, "(E23*D24+K24*L23)/G23");
            setCellValue(row, 7, 7.02791412596516);
//            setCellValue(row, 10, 7.38580687071866);
        } else if ("R2O3(%)".equals(grade)) {
            setCellFormula(row, 3, "IF(ISERROR((D29*E27+D33*E31)/E23),0,((D29*E27+D33*E31)/E23))");
            setCellFormula(row, 5, "(E23*D25+K25*L23)/G23");
            setCellValue(row, 7, 1.09547174628559);
//            setCellValue(row, 10, 1.12794541352464);
        } else if ("SiO2(%)".equals(grade)) {
            setCellFormula(row, 3, "IF(ISERROR((D30*E27+D34*E31)/E23),0,((D30*E27+D34*E31)/E23))");
            setCellFormula(row, 5, "(E23*D26+K26*L23)/G23");
            setCellValue(row, 7, 5.81945470798828);
//            setCellValue(row, 10, 8.75412708640106);
        }
    }

    // === 1 自产矿石 ===
    private void setupSelfProducedOreRow(Row row, String sequence, String productName, String grade) {
        setCellValue(row, 0, sequence);
        setCellValue(row, 1, productName);
        setCellValue(row, 2, grade);

        setCellFormula(row, 3, "IF(ISERROR((D39*E39+E51*D51)/E27),0,((D39*E39+E51*D51)/E27))");
        setCellFormula(row, 4, "E39+E51");
        setCellFormula(row, 5, "IF(ISERROR((E27*D27+K27*L27)/G27),0,(E27*D27+K27*L27)/G27)");
        setCellFormula(row, 6, "E27+L27");

        setCellValue(row, 7, 27.9045253296141);
        setCellValue(row, 8, 75083.26);
//        setCellValue(row, 10, 20.0748382050207);
//        setCellValue(row, 11, 54051.43);
    }

    private void setupSelfProducedOreSubRow(Row row, String grade) {
        setCellValue(row, 2, grade);

        if ("MgO(%)".equals(grade)) {
            setCellFormula(row, 3, "IF(ISERROR((D40*E39+E51*D52)/E27),0,((D40*E39+E51*D52)/E27))");
            setCellFormula(row, 5, "IF(ISERROR((E27*D28+K28*L27)/G27),0,(E27*D28+K28*L27)/G27)");
            setCellValue(row, 7, 4.7657281596457);
//            setCellValue(row, 10, 8.99047635742477);
        } else if ("R2O3(%)".equals(grade)) {
            setCellFormula(row, 3, "IF(ISERROR((D41*E39+E51*D53)/E27),0,((D41*E39+E51*D53)/E27))");
            setCellFormula(row, 5, "IF(ISERROR((E27*D29+K29*L27)/G27),0,(E27*D29+K29*L27)/G27)");
            setCellValue(row, 7, 0.557671289179505);
//            setCellValue(row, 10, 0.499141526875422);
        } else if ("SiO2(%)".equals(grade)) {
            setCellFormula(row, 3, "IF(ISERROR((D42*E39+E51*D54)/E27),0,((D42*E39+E51*D54)/E27))");
            setCellFormula(row, 5, "IF(ISERROR((E27*D30+K30*L27)/G27),0,(E27*D30+K30*L27)/G27)");
            setCellValue(row, 7, 6.20742467894974);
//            setCellValue(row, 10, 6.8939088124033);
        }
    }

    // === 2 外部矿石利用 ===
    private void setupExternalOreRow(Row row, String sequence, String productName, String grade) {
        setCellValue(row, 0, sequence);
        setCellValue(row, 1, productName);
        setCellValue(row, 2, grade);

        setCellFormula(row, 3, "IF(ISERROR((D43*E43+E55*D55)/E31),0,((D43*E43+E55*D55)/E31))");
        setCellFormula(row, 4, "E43+E55");
        setCellFormula(row, 5, "(E31*D31+K31*L31)/G31");
        setCellFormula(row, 6, "E31+L31");

        setCellValue(row, 7, 21.9661943279442);
        setCellValue(row, 8, 294853.94);
//        setCellValue(row, 10, 22.7533844156879);
//        setCellValue(row, 11, 70776.56);
    }

    private void setupExternalOreSubRow(Row row, String grade) {
        setCellValue(row, 2, grade);

        if ("MgO(%)".equals(grade)) {
            setCellFormula(row, 3, "IF(ISERROR((D44*E43+E55*D56)/E31),0,((D44*E43+E55*D56)/E31))");
            setCellFormula(row, 5, "(E31*D32+K32*L31)/G31");
            setCellValue(row, 7, 7.60396984045728);
//            setCellValue(row, 10, 6.16033504171438);
        } else if ("R2O3(%)".equals(grade)) {
            setCellFormula(row, 3, "IF(ISERROR((D45*E43+E55*D57)/E31),0,((D45*E43+E55*D57)/E31))");
            setCellFormula(row, 5, "(E31*D33+K33*L31)/G31");
            setCellValue(row, 7, 1.23242026916785);
//            setCellValue(row, 10, 1.60815735463832);
        } else if ("SiO2(%)".equals(grade)) {
            setCellFormula(row, 3, "IF(ISERROR((D46*E43+E55*D58)/E31),0,((D46*E43+E55*D58)/E31))");
            setCellFormula(row, 5, "(E31*D34+K34*L31)/G31");
            setCellValue(row, 7, 5.72065985992929);
//            setCellValue(row, 10, 10.1747592536286);
        }
    }

    // === 1 双山坪公司 ===
    private void setupShuangshanpingRow(Row row, String sequence, String productName, String grade) {
        setCellValue(row, 0, sequence);
        setCellValue(row, 1, productName);
        setCellValue(row, 2, grade);

        setCellFormula(row, 3, "IF(ISERROR((D39*E39+D43*E43)/E35),0,((D39*E39+D43*E43)/E35))");
        setCellFormula(row, 4, "E39+E43");
        setCellFormula(row, 5, "(E35*D35+K35*L35)/G35");
        setCellFormula(row, 6, "E35+L35");

        setCellValue(row, 7, 23.4409679533689);
        setCellValue(row, 8, 228105.1);
//        setCellValue(row, 10, 21.5935543727012);
//        setCellValue(row, 11, 124827.99);
    }

    private void setupShuangshanpingSubRow(Row row, String grade) {
        setCellValue(row, 2, grade);

        if ("MgO(%)".equals(grade)) {
            setCellFormula(row, 3, "IF(ISERROR((D40*E39+D44*E43)/E35),0,((D40*E39+D44*E43)/E35))");
            setCellFormula(row, 5, "(E35*D36+K36*L35)/G35");
            setCellValue(row, 7, 6.96167637505694);
//            setCellValue(row, 10, 7.38580687071866);
        } else if ("R2O3(%)".equals(grade)) {
            setCellFormula(row, 3, "IF(ISERROR((D41*E39+D45*E43)/E35),0,((D41*E39+D45*E43)/E35))");
            setCellFormula(row, 5, "(E35*D37+K37*L35)/G35");
            setCellValue(row, 7, 1.10794783588793);
//            setCellValue(row, 10, 1.12794541352464);
        } else if ("SiO2(%)".equals(grade)) {
            setCellFormula(row, 3, "IF(ISERROR((D42*E39+D46*E43)/E35),0,((D42*E39+D46*E43)/E35))");
            setCellFormula(row, 5, "(E35*D38+K38*L35)/G35");
            setCellValue(row, 7, 5.29567312918475);
//            setCellValue(row, 10, 8.75412708640106);
        }
    }

    // === 1.1 自产矿石（双山坪公司）===
    private void setupShuangshanpingSelfProducedRow(Row row, String sequence, String productName, String grade) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        setCellValue(row, 0, sequence);
        setCellValue(row, 1, productName);
        setCellValue(row, 2, grade);


        setCellValue(row, 3, data.get("C24"));
        setCellValue(row, 4, data.get("B24"));
        setCellFormula(row, 5, "IF(ISERROR((E39*D39+K39*L39)/G39),0,((E39*D39+K39*L39)/G39))");
        setCellFormula(row, 6, "E39+L39");

        setCellValue(row, 7, 27.9045253296141);
        setCellValue(row, 8, 75083.26);
//        setCellValue(row, 10, 20.0748382050207);
//        setCellValue(row, 11, 54051.43);
    }

//    /**
//     * @Description: 用来获取data中的具体数据。
//     * @data:[i, field]
//     * @return: java.lang.Object
//     * @Author: zhongjj6
//     * @Date: 2025/9/28 15:58
//     */
//    private Object getData(int i, String field) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
//        Object obj = data.get(i);
//        java.lang.reflect.Method method = obj.getClass().getMethod(field);
//        return method.invoke(obj);
//    }

    private void setupShuangshanpingSelfProducedSubRow(Row row, String grade) {
        setCellValue(row, 2, grade);

        if ("MgO(%)".equals(grade)) {
            setCellValue(row, 3, data.get("D24"));
            setCellFormula(row, 5, "IF(ISERROR((E39*D40+K40*L39)/G39),0,((E39*D40+K40*L39)/G39))");
            setCellValue(row, 7, 4.7657281596457);
//            setCellValue(row, 10, 8.99047635742477);
        } else if ("R2O3(%)".equals(grade)) {
            setCellValue(row, 3, data.get("E24"));
            setCellFormula(row, 5, "IF(ISERROR((E41*D41+K41*L41)/G41),0,((E41*D41+K41*L41)/G41))");
            setCellValue(row, 7, 0.0);
//            setCellValue(row, 10, 0.0);
        } else if ("SiO2(%)".equals(grade)) {
            setCellValue(row, 3, data.get("F24"));
            setCellFormula(row, 5, "IF(ISERROR((E42*D42+K42*L42)/G42),0,((E42*D42+K42*L42)/G42))");
            setCellValue(row, 7, 0.0);
//            setCellValue(row, 10, 0.0);
        }
    }

    // === 1.2 外部矿石利用（双山坪公司）===
    private void setupShuangshanpingExternalOreRow(Row row, String sequence, String productName, String grade) {
        setCellValue(row, 0, sequence);
        setCellValue(row, 1, productName);
        setCellValue(row, 2, grade);

        setCellValue(row, 3, data.get("AS27"));

        setCellValue(row, 4, data.get("AR27"));

        setCellFormula(row, 5, "(E43*D43+K43*L43)/G43");
        setCellFormula(row, 6, "E43+L43");

        setCellValue(row, 7, 21.2508332705972);
        setCellValue(row, 8, 153021.84);
//        setCellValue(row, 10, 22.7533844156879);
//        setCellValue(row, 11, 70776.56);
    }

    private void setupShuangshanpingExternalOreSubRow(Row row, String grade) {
        setCellValue(row, 2, grade);

        if ("MgO(%)".equals(grade)) {
            setCellValue(row, 3, data.get("AT27"));

            setCellFormula(row, 5, "(E43*D44+K44*L43)/G43");
            setCellValue(row, 7, 8.0391627704908);
//            setCellValue(row, 10, 6.16033504171438);
        } else if ("R2O3(%)".equals(grade)) {
            setCellValue(row, 3, data.get("AU27"));

            setCellFormula(row, 5, "(E43*D45+K45*L43)/G43");
            setCellValue(row, 7, 1.37795215049041);
//            setCellValue(row, 10, 1.60815735463832);
        } else if ("SiO2(%)".equals(grade)) {
            setCellValue(row, 3, data.get("AV27"));

            setCellFormula(row, 5, "(E43*D46+K46*L43)/G43");
            setCellValue(row, 7, 4.84830379506612);
//            setCellValue(row, 10, 10.1747592536286);
        }
    }

    // === 2 有福公司 ===
    private void setupYoufuCompanyRow(Row row, String sequence, String productName, String grade) {
        setCellValue(row, 0, sequence);
        setCellValue(row, 1, productName);
        setCellValue(row, 2, grade);

        setCellFormula(row, 3, "IF(ISERROR((D51*E51+D55*E55)/E47),0,((D51*E51+D55*E55)/E47))");
        setCellFormula(row, 4, "E51+E55");
        setCellFormula(row, 5, "IF(ISERROR((E47*D47+K47*L47)/G47),0,((E47*D47+K47*L47)/G47))");
        setCellFormula(row, 6, "E47+L47");

        setCellValue(row, 7, 22.737993273737);
        setCellValue(row, 8, 141832.1);
//        setCellValue(row, 10, 0.0);
//        setCellValue(row, 11, 0.0);
    }

    private void setupYoufuCompanySubRow(Row row, String grade) {
        setCellValue(row, 2, grade);

        if ("MgO(%)".equals(grade)) {
            setCellFormula(row, 3, "IF(ISERROR((D52*E51+D56*E55)/E47),0,((D52*E51+D56*E55)/E47))");
            setCellFormula(row, 5, "IF(ISERROR((E47*D48+K48*L47)/G47),0,((E47*D48+K48*L47)/G47))");
            setCellValue(row, 7, 7.13444268187526);
//            setCellValue(row, 10, 0.0);
        } else if ("R2O3(%)".equals(grade)) {
            setCellFormula(row, 3, "IF(ISERROR((D53*E51+D57*E55)/E47),0,((D53*E51+D57*E55)/E47))");
            setCellFormula(row, 5, "IF(ISERROR((E49*D49+K49*L47)/G49),0,((E49*D49+K49*L47)/G49))");
            setCellValue(row, 7, 1.07540675629847);
//            setCellValue(row, 10, 0.0);
        } else if ("SiO2(%)".equals(grade)) {
            setCellFormula(row, 3, "IF(ISERROR((D54*E51+D58*E55)/E47),0,((D54*E51+D58*E55)/E47))");
            setCellFormula(row, 5, "IF(ISERROR((E50*D50+K50*L47)/G50),0,((E50*D50+K50*L47)/G50))");
            setCellValue(row, 7, 6.66183981975871);
//            setCellValue(row, 10, 0.0);
        }
    }

    // === 2.1 自产矿石（有福公司）===
    private void setupYoufuSelfProducedRow(Row row, String sequence, String productName, String grade) {
        setCellValue(row, 0, sequence);
        setCellValue(row, 1, productName);
        setCellValue(row, 2, grade);

        setCellValue(row, 3, data.get("BW25"));

        setCellValue(row, 4, data.get("BV25"));

        setCellFormula(row, 5, "IF(ISERROR((E51*D51+K51*L51)/G51),0,(E51*D51+K51*L51)/G51)");
        setCellFormula(row, 6, "E51+L51");

        setCellValue(row, 7, 0.0);
        setCellValue(row, 8, 0.0);
//        setCellValue(row, 10, 0.0);
//        setCellValue(row, 11, 0.0);
    }

    private void setupYoufuSelfProducedSubRow(Row row, String grade) {
        setCellValue(row, 2, grade);

        if ("MgO(%)".equals(grade)) {
            setCellValue(row, 3, data.get("BX25"));

            setCellFormula(row, 5, "IF(ISERROR((E51*D52+K52*L51)/G51),0,(E51*D52+K52*L51)/G51)");
            setCellValue(row, 7, 0.0);
//            setCellValue(row, 10, 0.0);
        } else if ("R2O3(%)".equals(grade)) {
            setCellValue(row, 3, data.get("BY25"));

            setCellValue(row, 3, data.get("BX25"));

            setCellFormula(row, 5, "IF(ISERROR((E51*D53+K53*L51)/G51),0,(E51*D53+K53*L51)/G51)");
            setCellValue(row, 7, 0.0);
//            setCellValue(row, 10, 0.0);
        } else if ("SiO2(%)".equals(grade)) {
            setCellValue(row, 3, data.get("BZ25"));

            setCellFormula(row, 5, "IF(ISERROR((E51*D54+K54*L51)/G51),0,(E51*D54+K54*L51)/G51)");
            setCellValue(row, 7, 0.0);
//            setCellValue(row, 10, 0.0);
        }
    }

    // === 2.2 外部矿石利用（有福公司）===
    private void setupYoufuExternalOreRow(Row row, String sequence, String productName, String grade) {
        setCellValue(row, 0, sequence);
        setCellValue(row, 1, productName);
        setCellValue(row, 2, grade);

        setCellValue(row, 3, data.get("AS26"));

        setCellValue(row, 4, data.get("AR26"));

        setCellFormula(row, 5, "IF(ISERROR((E55*D55+K55*L55)/G55),0,((E55*D55+K55*L55)/G55))");
        setCellFormula(row, 6, "E55+L55");

        setCellValue(row, 7, 22.737993273737);
        setCellValue(row, 8, 141832.1);
//        setCellValue(row, 10, 0.0);
//        setCellValue(row, 11, 0.0);
    }

    private void setupYoufuExternalOreSubRow(Row row, String grade) {
        setCellValue(row, 2, grade);

        if ("MgO(%)".equals(grade)) {
            setCellValue(row, 3, data.get("AT26"));

            setCellFormula(row, 5, "IF(ISERROR((E55*D56+K56*L55)/G55),0,((E55*D56+K56*L55)/G55))");
            setCellValue(row, 7, 7.13444268187526);
//            setCellValue(row, 10, 0.0);
        } else if ("R2O3(%)".equals(grade)) {
            setCellValue(row, 3, data.get("AU26"));
            setCellFormula(row, 5, "IF(ISERROR((E55*D57+K57*L55)/G55),0,((E55*D57+K57*L55)/G55))");
            setCellValue(row, 7, 1.07540675629847);
//            setCellValue(row, 10, 0.0);
        } else if ("SiO2(%)".equals(grade)) {
            setCellValue(row, 3, data.get("AV26"));

            setCellFormula(row, 5, "IF(ISERROR((E55*D58+K58*L55)/G55),0,((E55*D58+K58*L55)/G55))");
            setCellValue(row, 7, 6.66183981975871);
//            setCellValue(row, 10, 0.0);
        }
    }

    // === 应用所有合并单元格 ===
    private void applyAllMergeRegions(Sheet sheet) {
        System.out.println("应用合并区域...");

        // 一、受托加工磷矿石 (行4-6) - 原来3-5，现在+1
        sheet.addMergedRegion(new CellRangeAddress(4, 6, 0, 0)); // 序号（A列）
        sheet.addMergedRegion(new CellRangeAddress(4, 6, 1, 1)); // 产品名称（B列）

        // 1 黄磷公司 (行7-9) - 原来6-8，现在+1
        sheet.addMergedRegion(new CellRangeAddress(7, 9, 0, 0)); // 序号
        sheet.addMergedRegion(new CellRangeAddress(7, 9, 1, 1)); // 产品名称

        // 1.1 a层矿 (行10-12) - 原来9-11，现在+1
        sheet.addMergedRegion(new CellRangeAddress(10, 12, 0, 0)); // 序号
        sheet.addMergedRegion(new CellRangeAddress(10, 12, 1, 1)); // 产品名称

        // 其中a层矿返粉 (行13-15) - 原来12-14，现在+1
        sheet.addMergedRegion(new CellRangeAddress(13, 15, 1, 1)); // 产品名称

        // 1.2 b层矿 (行16-18) - 原来15-17，现在+1
        sheet.addMergedRegion(new CellRangeAddress(16, 18, 0, 0)); // 序号
        sheet.addMergedRegion(new CellRangeAddress(16, 18, 1, 1)); // 产品名称

        // 其中划拨b层矿 (行19-21) - 原来18-20，现在+1
        sheet.addMergedRegion(new CellRangeAddress(19, 21, 1, 1)); // 产品名称

        // 二、购进磷矿石 (行22-25) - 原来21-24，现在+1
        sheet.addMergedRegion(new CellRangeAddress(22, 25, 0, 0)); // 序号
        sheet.addMergedRegion(new CellRangeAddress(22, 25, 1, 1)); // 产品名称

        // 1 自产矿石 (行26-29) - 原来25-28，现在+1
        sheet.addMergedRegion(new CellRangeAddress(26, 29, 0, 0)); // 序号
        sheet.addMergedRegion(new CellRangeAddress(26, 29, 1, 1)); // 产品名称

        // 2 外部矿石利用 (行30-33) - 原来29-32，现在+1
        sheet.addMergedRegion(new CellRangeAddress(30, 33, 0, 0)); // 序号
        sheet.addMergedRegion(new CellRangeAddress(30, 33, 1, 1)); // 产品名称

        // 1 双山坪公司 (行34-37) - 原来33-36，现在+1
        sheet.addMergedRegion(new CellRangeAddress(34, 37, 0, 0)); // 序号
        sheet.addMergedRegion(new CellRangeAddress(34, 37, 1, 1)); // 产品名称

        // 1.1 自产矿石 (行38-41) - 原来37-40，现在+1
        sheet.addMergedRegion(new CellRangeAddress(38, 41, 0, 0)); // 序号
        sheet.addMergedRegion(new CellRangeAddress(38, 41, 1, 1)); // 产品名称

        // 1.2 外部矿石利用 (行42-45) - 原来41-44，现在+1
        sheet.addMergedRegion(new CellRangeAddress(42, 45, 0, 0)); // 序号
        sheet.addMergedRegion(new CellRangeAddress(42, 45, 1, 1)); // 产品名称

        // 2 有福公司 (行46-49) - 原来45-48，现在+1
        sheet.addMergedRegion(new CellRangeAddress(46, 49, 0, 0)); // 序号
        sheet.addMergedRegion(new CellRangeAddress(46, 49, 1, 1)); // 产品名称

        // 2.1 自产矿石 (行50-53) - 原来49-52，现在+1
        sheet.addMergedRegion(new CellRangeAddress(50, 53, 0, 0)); // 序号
        sheet.addMergedRegion(new CellRangeAddress(50, 53, 1, 1)); // 产品名称

        // 2.2 外部矿石利用 (行54-57) - 原来53-56，现在+1
        sheet.addMergedRegion(new CellRangeAddress(54, 57, 0, 0)); // 序号
        sheet.addMergedRegion(new CellRangeAddress(54, 57, 1, 1)); // 产品名称
    }

    private void setTitleCellStyle(Workbook workbook, Cell cell) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 14);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        cell.setCellStyle(style);
    }

    /**
     * 设置表头单元格样式（简化版，因为已经在创建时设置了样式）
     */
    private void setHeaderCellStyle(Workbook workbook, Row row) {
        // 这个方法可以简化，因为样式已经在创建单元格时设置了
        CellStyle style = createBlackBorderStyle(workbook);
        for (int i = 0; i < 12; i++) {
            Cell cell = row.getCell(i);
            if (cell == null) {
                cell = row.createCell(i);
            }
            cell.setCellStyle(style);
        }
    }
}
